package org.njo.webapp.root.model.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.ho.yaml.Yaml;
import org.njo.webapp.root.utility.ObjectNotExistsException;

public class DTSFieldDAO {

    
    
    /**
     * 数据库连接对象.
     */
    private Connection connection = null;

    /**
     * 构造函数.<br>
     * 需要一个数据库连接对象作为参数.
     * 
     * @param argConnection
     *            数据库连接对象
     */
    public DTSFieldDAO(Connection argConnection) {
        connection = argConnection;
    }
    
    
    /**
     * 向数据库表中插入一条记录.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int insertField(String argFieldName, String argYAMLString, String argFieldDescription)
            throws SQLException {
        StringBuffer insertSql = null;
        PreparedStatement insertStatement = null;
        try {
            insertSql = new StringBuffer();
            insertSql.append(" INSERT INTO DT_FILED_DICT ( ");
            insertSql.append(" NAME , ");
            insertSql.append(" YAML , ");
            insertSql.append(" DESCRIPTION ) ");
            insertSql.append(" VALUES ( ");
            insertSql.append(" ? , ");
            insertSql.append(" ? , ");
            insertSql.append(" ? ) ");

            insertStatement = connection.prepareStatement(insertSql.toString());
            insertStatement.setString(1, argFieldName);
            insertStatement.setString(2, argYAMLString);
            insertStatement.setString(3, argFieldDescription);

            return insertStatement.executeUpdate();
        } finally {
            if (insertStatement != null) {
                insertStatement.close();
                insertStatement = null;
            }
        }
    }
    
    /**
     * 向数据库表中插入一条记录.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int insertFieldSet(String argFieldSetName, String argYAMLString, String argFieldSetDescription)
            throws SQLException {
        StringBuffer insertSql = null;
        PreparedStatement insertStatement = null;
        try {
            insertSql = new StringBuffer();
            insertSql.append(" INSERT INTO DT_FILED_SET_DICT ( ");
            insertSql.append(" FIELD_SET_NAME , ");
            insertSql.append(" YAML , ");
            insertSql.append(" DESCRIPTION ) ");
            insertSql.append(" VALUES ( ");
            insertSql.append(" ? , ");
            insertSql.append(" ? , ");
            insertSql.append(" ? ) ");

            insertStatement = connection.prepareStatement(insertSql.toString());
            insertStatement.setString(1, argFieldSetName);
            insertStatement.setString(2, argYAMLString);
            insertStatement.setString(3, argFieldSetDescription);

            return insertStatement.executeUpdate();
        } finally {
            if (insertStatement != null) {
                insertStatement.close();
                insertStatement = null;
            }
        }
    }
    
    /**
     * 从APP_PROPERTIES表,读取全部数据.
     * 
     * @param argStartIndex
     * @param argEndIndex
     * @return
     * @throws java.sql.SQLException
     */
    public List selectAllFields(int argStartIndex, int argEndIndex)
            throws SQLException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        List resultList = new ArrayList();
        int curIndex = 0;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT NAME , ");
            selectSql.append("        YAML, ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM DT_FILED_DICT ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            resultSet = selectStatement.executeQuery();
            if (resultSet.absolute(argStartIndex)) {
                curIndex = argStartIndex;
                do {
                    String[] message = new String[3];
                    message[0] = resultSet.getString(1);
                    message[1] = resultSet.getString(2);
                    message[2] = resultSet.getString(3);
                    resultList.add(message);
                    curIndex++;
                } while (resultSet.next() && (curIndex <= argEndIndex));
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return resultList;
    }
    
    /**
     * 从APP_PROPERTIES表,读取全部数据.
     * 
     * @param argStartIndex
     * @param argEndIndex
     * @return
     * @throws java.sql.SQLException
     */
    public List selectAllFieldSets(int argStartIndex, int argEndIndex)
            throws SQLException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        List resultList = new ArrayList();
        int curIndex = 0;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT FIELD_SET_NAME , ");
            selectSql.append("        YAML, ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM DT_FILED_SET_DICT ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            resultSet = selectStatement.executeQuery();
            if (resultSet.absolute(argStartIndex)) {
                curIndex = argStartIndex;
                do {
                    String[] message = new String[3];
                    message[0] = resultSet.getString(1);
                    message[1] = resultSet.getString(2);
                    message[2] = resultSet.getString(3);
                    resultList.add(message);
                    curIndex++;
                } while (resultSet.next() && (curIndex <= argEndIndex));
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return resultList;
    }
    
    
    /**
     * 根据指定的PropertyKey,取得数据.
     * 
     * @param argMessageKey
     * @return
     * @throws SQLException
     * @throws ObjectNotExistsException
     */
    public String[] selectFieldByName(String argFieldName) throws SQLException,
            ObjectNotExistsException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        String[] propertyData = null;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT NAME , ");
            selectSql.append("        YAML, ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM DT_FILED_DICT ");
            selectSql.append(" WHERE NAME = ? ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            selectStatement.setString(1, argFieldName);

            resultSet = selectStatement.executeQuery();

            if (resultSet.next()) {
                propertyData = new String[3];
                propertyData[0] = (resultSet.getString(1));
                propertyData[1] = (resultSet.getString(2));
                propertyData[2] = (resultSet.getString(3));
            } else {
                throw new ObjectNotExistsException("not found data in database.");
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return propertyData;
    }
    
    /**
     * 根据指定的PropertyKey,取得数据.
     * 
     * @param argMessageKey
     * @return
     * @throws SQLException
     * @throws ObjectNotExistsException
     */
    public String[] selectFieldSetByName(String argFieldSetName) throws SQLException,
            ObjectNotExistsException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        String[] propertyData = null;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT FIELD_SET_NAME , ");
            selectSql.append("        YAML, ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM DT_FILED_SET_DICT ");
            selectSql.append(" WHERE FIELD_SET_NAME = ? ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            selectStatement.setString(1, argFieldSetName);

            resultSet = selectStatement.executeQuery();

            if (resultSet.next()) {
                propertyData = new String[3];
                propertyData[0] = (resultSet.getString(1));
                propertyData[1] = (resultSet.getString(2));
                propertyData[2] = (resultSet.getString(3));
            } else {
                throw new ObjectNotExistsException("not found data in database.");
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return propertyData;
    }
    
    /**
     * 更新数据库表中一条记录.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int updateField(String argFieldName, String argFieldYAMLString, String argFieldDescription)
            throws SQLException {
        StringBuffer updateSql = null;
        PreparedStatement updateStatement = null;
        try {
            updateSql = new StringBuffer();
            updateSql.append(" UPDATE DT_FILED_DICT SET ");
            updateSql.append(" YAML = ?, ");
            updateSql.append(" DESCRIPTION = ? ");
            updateSql.append(" WHERE NAME = ? ");

            updateStatement = connection.prepareStatement(updateSql.toString());
            updateStatement.setString(1, argFieldYAMLString);
            updateStatement.setString(2, argFieldDescription);
            updateStatement.setString(3, argFieldName);

            return updateStatement.executeUpdate();
        } finally {
            if (updateStatement != null) {
                updateStatement.close();
                updateStatement = null;
            }
        }

    }
    
    /**
     * 更新数据库表中一条记录.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int updateFieldSet(String argFieldSetName, String argFieldSetYAMLString, String argFieldSetDescription)
            throws SQLException {
        StringBuffer updateSql = null;
        PreparedStatement updateStatement = null;
        try {
            updateSql = new StringBuffer();
            updateSql.append(" UPDATE DT_FILED_SET_DICT SET ");
            updateSql.append(" YAML = ?, ");
            updateSql.append(" DESCRIPTION = ? ");
            updateSql.append(" WHERE FIELD_SET_NAME = ? ");

            updateStatement = connection.prepareStatement(updateSql.toString());
            updateStatement.setString(1, argFieldSetYAMLString);
            updateStatement.setString(2, argFieldSetDescription);
            updateStatement.setString(3, argFieldSetName);

            return updateStatement.executeUpdate();
        } finally {
            if (updateStatement != null) {
                updateStatement.close();
                updateStatement = null;
            }
        }
    }
    
    /**
     * 删除数据.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int deleteFieldByName(String argFieldName)
            throws SQLException {
        StringBuffer deleteSql = null;
        PreparedStatement deleteStatement = null;
        try {
            deleteSql = new StringBuffer();
            deleteSql.append(" DELETE FROM DT_FILED_DICT ");
            deleteSql.append(" WHERE NAME = ? ");
            deleteStatement = connection.prepareStatement(deleteSql.toString());
            deleteStatement.setString(1, argFieldName);
            return deleteStatement.executeUpdate();
        }
        finally {
            if (deleteStatement != null) {
                deleteStatement.close();
                deleteStatement = null;
            }
        }
    }

    /**
     * 删除数据.<br>
     * 
     * @param messageKey
     * @param description
     * @return 被插入数据的条数
     * @throws SQLException
     *             SQL异常
     */
    public int deleteFieldSetByName(String argFieldSetName)
            throws SQLException {
        StringBuffer deleteSql = null;
        PreparedStatement deleteStatement = null;
        try {
            deleteSql = new StringBuffer();
            deleteSql.append(" DELETE FROM DT_FILED_SET_DICT ");
            deleteSql.append(" WHERE FIELD_SET_NAME = ? ");
            deleteStatement = connection.prepareStatement(deleteSql.toString());
            deleteStatement.setString(1, argFieldSetName);
            return deleteStatement.executeUpdate();
        }
        finally {
            if (deleteStatement != null) {
                deleteStatement.close();
                deleteStatement = null;
            }
        }
    }
    
    public List selectFieldType() throws SQLException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        List resultList = new ArrayList();
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT FIELD_TYPE , ");
            selectSql.append("        ALLOW_HAS_SIZE, ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM DT_FIELD_TYPE ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            resultSet = selectStatement.executeQuery();
            while (resultSet.next()) {
                Map typeMap = new HashMap();
                typeMap.put("field_type", resultSet.getString("FIELD_TYPE"));
                typeMap.put("allow_has_size", resultSet.getString("ALLOW_HAS_SIZE"));
                typeMap.put("description", resultSet.getString("DESCRIPTION"));
                resultList.add(typeMap);
            }
            
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return resultList;
        
    }
    
    
    public List selectDBKind() throws SQLException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        List resultList = new ArrayList();
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT TO_KIND , ");
            selectSql.append("        TO_KIND_TYPE, ");
            selectSql.append("        DESCRIPTION ");
            selectSql.append(" FROM DT_TO_DIND ");
            selectSql.append(" WHERE TO_KIND_TYPE = 'db' ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            resultSet = selectStatement.executeQuery();
            while (resultSet.next()) {
                Map typeMap = new HashMap();
                typeMap.put("to_kind", resultSet.getString("TO_KIND"));
                typeMap.put("to_kind_type", resultSet.getString("TO_KIND_TYPE"));
                typeMap.put("description", resultSet.getString("DESCRIPTION"));
                resultList.add(typeMap);
            }
            
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return resultList;
        
    }
        
    public int insertGenSQLDDL(String argGenName, String argYAMLString) throws SQLException {
        StringBuffer insertSql = null;
        PreparedStatement insertStatement = null;
        try {
            insertSql = new StringBuffer();
            insertSql.append(" INSERT INTO DT_GT_SQL_DDL ( ");
            insertSql.append(" GEN_NAME , ");
            insertSql.append(" YAML ) ");
            insertSql.append(" VALUES ( ");
            insertSql.append(" ? , ");
            insertSql.append(" ? ) ");

            insertStatement = connection.prepareStatement(insertSql.toString());
            insertStatement.setString(1, argGenName);
            insertStatement.setString(2, argYAMLString);

            return insertStatement.executeUpdate();
        } finally {
            if (insertStatement != null) {
                insertStatement.close();
                insertStatement = null;
            }
        }
    }
    
    public int updateGenSQLDDL(String argGenName, String argYAMLString)
            throws SQLException {
        StringBuffer updateSql = null;
        PreparedStatement updateStatement = null;
        try {
            updateSql = new StringBuffer();
            updateSql.append(" UPDATE DT_GT_SQL_DDL SET ");
            updateSql.append(" YAML = ? ");
            updateSql.append(" WHERE GEN_NAME = ? ");

            updateStatement = connection.prepareStatement(updateSql.toString());
            updateStatement.setString(1, argYAMLString);
            updateStatement.setString(2, argGenName);

            return updateStatement.executeUpdate();
        } finally {
            if (updateStatement != null) {
                updateStatement.close();
                updateStatement = null;
            }
        }
    }
    
    public int deleteGenSQLDDLByName(String argGenName) throws SQLException {
        StringBuffer deleteSql = null;
        PreparedStatement deleteStatement = null;
        try {
            deleteSql = new StringBuffer();
            deleteSql.append(" DELETE FROM DT_GT_SQL_DDL ");
            deleteSql.append(" WHERE GEN_NAME = ? ");
            deleteStatement = connection.prepareStatement(deleteSql.toString());
            deleteStatement.setString(1, argGenName);
            return deleteStatement.executeUpdate();
        } finally {
            if (deleteStatement != null) {
                deleteStatement.close();
                deleteStatement = null;
            }
        }
    }
    
    public List selectDDLGENList()
            throws SQLException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        List resultList = new ArrayList();
        int curIndex = 0;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT GEN_NAME , ");
            selectSql.append("        YAML ");
            selectSql.append(" FROM DT_GT_SQL_DDL ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);
            resultSet = selectStatement.executeQuery();
            
            while (resultSet.next()) {
                String yamlStr = resultSet.getString("YAML");
                Map gen = (Map)Yaml.load(yamlStr);
                resultList.add(gen);
            
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return resultList;
    }
    
    public Map selectDDLGENByName(String argGenName) throws SQLException,
            ObjectNotExistsException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        Map record = null;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT GEN_NAME , ");
            selectSql.append("        YAML ");
            selectSql.append(" FROM DT_GT_SQL_DDL ");
            selectSql.append(" WHERE GEN_NAME = ? ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            selectStatement.setString(1, argGenName);

            resultSet = selectStatement.executeQuery();

            if (resultSet.next()) {
                String yamlStr = resultSet.getString("YAML");
                record = (Map)Yaml.load(yamlStr);
            } else {
                throw new ObjectNotExistsException(
                        "not found data in database.");
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return record;
    }

    
    
    public Map selectFTMByName(String argFieldType, String argToKind) throws SQLException,
            ObjectNotExistsException {
        StringBuffer selectSql = null;
        PreparedStatement selectStatement = null;
        ResultSet resultSet = null;
        Map record = null;
        try {
            selectSql = new StringBuffer();
            selectSql.append(" SELECT FIELD_TYPE , ");
            selectSql.append("        TO_KIND, ");
            selectSql.append("        TO_KIND_FIELD_TYPE ");
            selectSql.append(" FROM DT_FIELD_TYPE_MAPPING ");
            selectSql.append(" WHERE FIELD_TYPE = ? ");
            selectSql.append(" AND TO_KIND = ? ");
            selectStatement = connection.prepareStatement(selectSql.toString(),
                    ResultSet.TYPE_SCROLL_INSENSITIVE,
                    ResultSet.CONCUR_READ_ONLY);

            selectStatement.setString(1, argFieldType);
            selectStatement.setString(2, argToKind);

            
            resultSet = selectStatement.executeQuery();

            if (resultSet.next()) {
                record = new HashMap();
                record.put("field_type", resultSet.getString("FIELD_TYPE"));
                record.put("to_kind", resultSet.getString("TO_KIND"));
                record.put("to_kind_field_type", resultSet.getString("TO_KIND_FIELD_TYPE"));
            } else {
                throw new ObjectNotExistsException(
                        "not found data in database.");
            }
        } finally {
            if (resultSet != null) {
                resultSet.close();
                resultSet = null;
            }
            if (selectStatement != null) {
                selectStatement.close();
                selectStatement = null;
            }
        }
        return record;
    }

    
    
    
}
